package com.exercise.module.common.util.poi;/**
 * Created by kete on 2017-05-25.
 */

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.util.*;

/**
 * 作者：kete
 * 创建时间：2017-05-25 10:37
 * 功能描述：
 * 版本：
 */
public class ExcelUtil {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    private static int DEFAULT_SHEET_INDEX = 1;

    private static String DEFAULT_SHEET_NAME = "Sheet1";

    private static  Workbook wb = null;

    private static Sheet sheet = null;




    public static  <T> List<T> importFile(String filePath,Class<T> classType){
        return importFile(filePath,DEFAULT_SHEET_INDEX,classType);
    }

    public static  <T> List<T> importFile(String filePath,int startIndex,Class<T> classType){
        return importFile(filePath,null,startIndex,classType);
    }


    public static List<Map> importFile(String filePath,String sheetName){
        return importFile(filePath,sheetName,DEFAULT_SHEET_INDEX);
    }


    public static List<Map> importFile(String filePath){
        return importFile(filePath,"");
    }


    public static List<Map> importFile(String filePath,String sheetName,int startIndex){
        createObject(filePath,sheetName);

        Iterator<Row> rows =  sheet.rowIterator();
        List<Map> list = new ArrayList<Map>();

        while(rows.hasNext()){
            Row row = rows.next();
            if(row.getRowNum() >= startIndex){
                Iterator<Cell> cells =  row.cellIterator();
                Map map = new HashMap();
                while(cells.hasNext()){
                    Cell cell = cells.next();
                    map.put(cell.getColumnIndex(), getCellData(cell));
                }
                list.add(map);
//				System.out.println();
            }else{
//				System.out.println(">>>>>>>>>>>>>>>>>         "+row.getRowNum() +"," + startIndex);
            }
        }

        return list;

    }

    public static <T> List<T> importFile(String filePath,String sheetName,int startIndex,Class<T> classType){

        createObject(filePath,sheetName);

        Iterator<Row> rows =  sheet.rowIterator();

        List<T> list = new ArrayList<T>();
        try {
            while(rows.hasNext()){
                Row row = rows.next();
                if(row.getRowNum() >= startIndex){
                    T object = null;
                    Field[] fields = classType.getDeclaredFields();
                    for(Field f : fields){
                        if(f.isAnnotationPresent(ExcelElement.class)){
                            ExcelElement ee = f.getAnnotation(ExcelElement.class);

                            int columnIndex  = ee.columnIndex();
                            Cell cell = row.getCell(columnIndex);
                            if( null != cell){
                                f.setAccessible(true);
                                Object value = getCellData(cell);

                                if(null != value){
                                    if(object == null) object = classType.newInstance();
                                    logger.info(value.getClass() +" is equals "+ f.getType() + ":" +(value.getClass()==f.getType()) +","+(value.getClass().equals(f.getType()) ) );
                                    if(value.getClass().equals(f.getType())){
                                        //如果类型相等则使用默认方法
                                        f.set(object, value);
                                    }else{
                                        setValue(object,f,value);
                                    }
                                }
                            }

                        }
                    }

                    if(object != null)  list.add(object);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }


        return list;

    }

    private static Object getCellData(Cell cell){
        Object object = new Object();
        if(cell == null) return object;
        switch (cell.getCellType()) {   //根据cell中的类型来输出数据
            case HSSFCell.CELL_TYPE_NUMERIC:
                if(HSSFDateUtil.isCellDateFormatted(cell)){
                    Date d = cell.getDateCellValue();
//	            	DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
//	            	System.out.print(formater.format(d));
                    object = d;
                }else{
                    DecimalFormat df = new DecimalFormat("0");
//	        		System.out.print(df.format(cell.getNumericCellValue()));
                    object = df.format(cell.getNumericCellValue());
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:
//	            System.out.print(cell.getStringCellValue());
                object = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
//	            System.out.print(cell.getBooleanCellValue());
                object = cell.getBooleanCellValue();
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
//	            System.out.print(cell.getCellFormula());
                object = cell.getCellFormula();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
//	        	System.out.print("$BLANK$");
                object = null;
                break;
            default:
                System.out.print("unsuported sell type");
                break;
        }
        return object;

    }

    private static void setValue(Object instance,Field field,Object value){

        try {
            if(field.getType() == Date.class){
                //日期做特殊处理
                if(value.getClass() == String.class){
                    value = value.toString().replaceAll("年|月", "/").replaceAll("日", "").replaceAll("-", "/");
                }
            }
            Class fclazz = field.getType();
            Constructor cons = fclazz.getConstructor(value.getClass());
            Object setValue = cons.newInstance(value);

            field.set(instance,setValue);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private static void createReader(InputStream input){
        try {
            wb = WorkbookFactory.create(input);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }finally {
            try {
                input.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private static void createObject(String filePath,String sheetName){
        try {
            createReader(new FileInputStream(filePath));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        if(StringUtils.isNotEmpty(sheetName)){
            sheet = wb.getSheet(sheetName);
        }else{
            sheet = wb.getSheet(DEFAULT_SHEET_NAME);
        }
    }

}
